package com.adminpanel.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.adminpanel.modelbean.ProductBean;
import com.adminpanel.modelbean.UserfileBean;


public class UserfileServices {
	private JdbcTemplate jdbcTemplate;
	private DataSource dataSource;

	private int totalPages;

	public int getTotalPages() {
		return totalPages;
	}

	@Resource(name = "dataSource")
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public void addUploadData(final int user_id, final String upploadfile_name,
			final String save_path, final String filesize,
			final String uploadtime) {
			jdbcTemplate.update("insert into adminpanel_userfile values(?,?,?,?,?,?)",
				new PreparedStatementSetter() {

					@Override
					public void setValues(PreparedStatement ps) {

						try {
							ps.setInt(1, 0);
							ps.setInt(2, user_id);
							ps.setString(3, upploadfile_name);
							ps.setString(4, save_path);
							ps.setString(5, filesize);
							ps.setString(6, uploadtime);

						} catch (SQLException e) {

							e.printStackTrace();
						}
					}

				});

	}

	public List<UserfileBean> getFileByUserId(int user_id) {

		String sql = "select * from adminpanel_userfile where user_id = ? order by id desc";
		RowMapper<UserfileBean> mapper = new RowMapper<UserfileBean>() {

			@Override
			public UserfileBean mapRow(ResultSet rs, int rowNum)
					throws SQLException {

				UserfileBean userfileBean = new UserfileBean();
				userfileBean.setId(rs.getInt("id"));
				userfileBean.setUser_id(rs.getInt("user_id"));
				userfileBean.setUpload_filename(rs.getString("upload_filename"));
				userfileBean.setSave_path(rs.getString("save_path"));
				userfileBean.setFilesize(rs.getString("filesize"));
				userfileBean.setUploadtime(rs.getString("uploadtime"));

				return userfileBean;

			}

		};

		return jdbcTemplate.query(sql, mapper, new Object[] { user_id });

	}
	
	
	public Pagination getPagination(int page, int rows) {
		String sql = "select * from traceip";
		return new Pagination(sql, page,rows, this.jdbcTemplate);
	}
	
	public List getResultByPage(int page,int rows){
		String sql = "select count(*) from adminpanel_userfile where user_id = ?";
		String countSQL = getSQLCount(sql);
		//setPage(page);
		setTotalPages(rows,jdbcTemplate.queryForInt(countSQL));
		int startIndex = (page - 1) * rows;	
		
		StringBuffer paginationSQL = new StringBuffer(" ");
		paginationSQL.append(sql);
		paginationSQL.append(" limit "+ startIndex+","+rows);
		//System.out.print(paginationSQL.toString());
		return jdbcTemplate.queryForList(paginationSQL.toString());
		
		
		
	}

	public void setTotalPages(int numPerPage, int totalRows) {
		if (totalRows % numPerPage == 0) {
			this.totalPages = totalRows / numPerPage;
		} else {
			this.totalPages = (totalRows / numPerPage) + 1;
		}
	}

	public String getSQLCount(String sql) {
		String sqlBak = sql.toLowerCase();
		String searchValue = " from ";
		String sqlCount = "select count(*) from "
				+ sql.substring(sqlBak.indexOf(searchValue)
						+ searchValue.length(), sqlBak.length());
		return sqlCount;
	}
	
	//返回符合结果的记录条数
	public int getUserfileCounts(int user_id) {
		String sql = "select count(*) from adminpanel_userfile where user_id = ?";
		return jdbcTemplate.queryForInt(sql, new Object[] { user_id });
	}

}
